from xiaoyiwy.xiaoyi.config import *
import time
#import logging
import json
import requests
import re
# import sys
# import os
# curPath = os.path.abspath(os.path.dirname(__file__))
# rootPath = os.path.split(curPath)[0]
# sys.path.append(rootPath)
# print(os.getcwd())

#initLog("./drug_name.log")
db = mysql()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了




# 获取sql字段
def get_index_dict(cursor):
    # 获取数据库对应表中的字段名
    index_dict = dict()
    index = 0
    for desc in cursor.description:
        index_dict[desc[0]] = index
        index = index + 1
    return index_dict

# 扫码搜索
def scan(img):
    data_json = {}
    openid = img['openid']
    barcode = img['barcode']
    url = "https://api.jisuapi.com/barcode2/query?appkey=2d4a7f2e12c6062f&barcode='%s'" % barcode
    rescode = {
        "201": "条码为空",
        "202": "条码不正确",
        "203": "该条码以下市",
        "204": "该条码已注册，但编码信息未按规定通报",
        "210": "没有信息"
    }
    conn = db.connection()
    cursor = conn.cursor()
    if openid and barcode:
        sql = "select drug_id from drug_barcode where barcode = '%s'" % barcode
        if cursor.execute(sql) > 0:
            # 取name查询本地药品表
            drug_id = cursor.fetchall()[0][0]
            sql1 = "select drug_barcode.drug_id drug_id,drug_instruct.* from drug_barcode " \
                   "join drug_instruct on drug_barcode.drug_id= drug_instruct.id " \
                   "where drug_barcode.drug_id = '%s'" % drug_id
            if cursor.execute(sql1) > 0:
                # 提取本地数据
                resdata = sql_index_dict(cursor)
                #sqldata = cursor.fetchall()
                #sqltitle = sql_index(cursor)
                # resdata = []
                # for sqlvalue in sqldata:  # 循环重构每一条sql返回数据字典
                #     resdatai = {}
                #     # index为字段列表的下标，value为字段列表的值
                #     for index, value in enumerate(sqltitle):
                #         # data[title]为{}的key，sqlvalue[index]为{}的value--{"data[title]":"sqlvalue[index]"}
                #         resdatai[value] = sqlvalue[index]
                #     resdata.append(resdatai)  # 把{"data[title]":"sqlvalue[index]"}添加到resdata = []列表
                drug_data = {
                    "code": 1,
                    "error": "",
                    "data": resdata
                }
                data_json = drug_data
                now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                update_sql = "update drug_barcode set update_time = '%s'" % now
                try:
                    cursor.execute(update_sql)
                    conn.commit()
                    mysql_close(cursor,conn)
                except:
                    conn.rollback()
            else:
                data_json = {"code": "0", "error": "该药品未识别试试输入药名搜索","data": ""}
        else: # 本地无数据
            response_data = requests.get(url)
            code = response_data.json()['status']
            result_data = response_data.json()['result']
            res_name = result_data['name']
            res_company = result_data['company']
            res_barcode = result_data['barcode']
            res_ename = result_data['ename']
            if code == 0:
                sql2 = "select * from drug_instruct where currency_name = '%s' and company = '%s' LIMIT 1" % (res_name,res_company)
                if cursor.execute(sql2) > 0:
                    resdata = sql_index_dict(cursor)
                    # sqldata = cursor.fetchall()
                    # sqltitle = sql_index(cursor)
                    # resdata = []
                    # for sqlvalue in sqldata:  # 循环重构每一条sql返回数据字典
                    #     resdatai = {}
                    #     # index为字段列表的下标，value为字段列表的值
                    #     for index, value in enumerate(sqltitle):
                    #         # data[title]为{}的key，sqlvalue[index]为{}的value--{"data[title]":"sqlvalue[index]"}
                    #         resdatai[value] = sqlvalue[index]
                    #     resdata.append(resdatai)  # 把{"data[title]":"sqlvalue[index]"}添加到resdata = []列表
                    data_json = {
                        "code": 1,
                        "error": "",
                        "data": resdata
                    }
                    try:
                        drug_id = resdata[0]['id']
                        drug_name = resdata[0]['currency_name']
                        sql3 = "select id from drug_barcode where drug_id = '%s'" % drug_id
                        if cursor.execute(sql3) > 0:
                            pass
                        else:
                            sql4 = "insert into drug_barcode (open_id,drug_id,drug_name,barcode) " \
                                   "value ('%s','%s','%s','%s')" % (openid,drug_id,drug_name,barcode)
                            try:
                                cursor.execute(sql4)
                                conn.commit()
                                mysql_close(cursor,conn)
                            except:
                                conn.rollback()
                    except:
                        pass
                    mysql_close(cursor, conn)
                else:
                    select_sql = "select id from drug_temporary where barcode = '%s'" % res_barcode
                    create_sql = "insert into drug_temporary (barcode,drug_name,ename,company) " \
                                 "value ('%s','%s','%s','%s')" % (res_barcode,res_name,res_ename,res_company)
                    if cursor.execute(select_sql) < 1:
                        try:
                            cursor.execute(create_sql)
                            conn.commit()
                        except:
                            conn.rollback()
                    data_json = { "code": "0", "error": "该药品未识别试试输入药名搜索","data": "" }
            else:
                for key in rescode:
                    if code == key:
                        data_json = { "code": 0, "error": rescode[key],"data": "" }
    else:
        data_json = { "code": "0", "error": "openid与barcode为空","data": "" }
    return data_json

# 列表/详情搜索
def search(search_data):
    drug_name = search_data['drug_name']
    drug_name = re.sub('\W+', '', drug_name).replace("_", ' ')  # 过滤特殊符号
    # paper = search_data["paper"]
    # page = search_data["num"]
    drug_id = search_data['drug_id']
    openid = search_data['openid']
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    conn = db.connection()
    cursor = conn.cursor()
    # 添加历史搜索
    if openid:
        select_sql = "select name from history_search where name = '%s'" % (drug_name)
        insert_sql = "insert into history_search (open_id,name) value ('%s','%s')" % (openid, drug_name)
        updata_sql = "UPDATE history_search SET update_time= '%s' WHERE name = '%s'" % (now, drug_name)
        cursor.execute(select_sql)
        conn.commit()
        data = cursor.fetchall()
        try:
            if len(data) == '0':
                cursor.execute(insert_sql)
                conn.commit()
            else:
                cursor.execute(updata_sql)
                conn.commit()
        except:
            conn.rollback()
    else:
        data = {
            "code": 0,
            "error": "openid为空",
            "data": ""
        }
        return data
    # 单个药品详情
    if drug_id:
        sql = "select * from drug_instruct where id= '%s'" % drug_id
        cursor.execute(sql)
        conn.commit()
        data = cursor.fetchall()
        desc = get_index_dict(cursor)
        res = []
        for datai in data:  # 重新生成字典
            resi = {}
            for indexi in desc:
                resi[indexi] = datai[desc[indexi]]
                # 过滤药名中的英文名称与商品名称保留通用名称
                # if indexi == 'drug_name':
                #     ss = datai[index_dict[indexi]].split("名称")
                #     content = ss[1]
                #     name = content[1:-2]
                #     resi[indexi] = name
            res.append(resi)
        # data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
        # # 过滤drug_name重新生成字典
        # for key,value in data_dict[0].items():
        #     if key == 'drug_name':
        #         drug_value = data_dict[0].get("drug_name")
        #         ss = drug_value.split("名称")
        #         content = ss[1]
        #         name = content[1:-2]
        #         data_dict[0]['drug_name'] = name
        #         data_dict = [data_dict[0]]
        drug_data = {
            "code": 1,
            "error": "",
            "data": res
        }
        # 添加热搜
        drug_name = res[0]['currency_name']
        count_sql = "select count(id) from hot_search where drug_id = '%s'" % (drug_id)
        insert_sql = "insert into hot_search (drug_id,drug_name) value ('%s','%s')" % (drug_id, drug_name)
        updata_sql = "UPDATE hot_search SET update_time = '%s',frequency = frequency+1  WHERE drug_id = '%s'" % (
        now, drug_id)
        cursor.execute(count_sql)
        conn.commit()
        count_data = cursor.fetchall()
        try:
            if count_data[0][0] > 0:
                cursor.execute(updata_sql)
                conn.commit()
            else:
                cursor.execute(insert_sql)
                conn.commit()
        except:
            conn.rollback()
        mysql_close(cursor, conn)
        return drug_data
    # 药品列表
    else:
        sql1 = "select id,currency_name,commodity_name,indication,company,composition from drug_instruct LIMIT 100"
        if len(drug_name) > 0:
            sql1 = "select id,currency_name,commodity_name,indication,company,composition from drug_instruct " \
                   "where currency_name like '%%%s%%' or commodity_name like '%%%s%%' or indication like '%%%s%%' or composition like '%%%s%%' LIMIT 100" % (
                   drug_name, drug_name, drug_name, drug_name)
        # else:
        #     sql1 = "select id,currency_name,commodity_name,indication,company,composition from drug_instruct LIMIT 100"
        cursor.execute(sql1)
        conn.commit()
        data = cursor.fetchall()
        index_dict = get_index_dict(cursor)
        res = []
        for datai in data:  # 重新生成字典
            resi = {}
            for indexi in index_dict:
                resi[indexi] = datai[index_dict[indexi]]
                # 过滤药名中的英文名称与商品名称保留通用名称
                # if indexi == 'drug_name':
                #     ss = datai[index_dict[indexi]].split("名称")
                #     content = ss[1]
                #     name = content[1:-2]
                #     resi[indexi] = name
            res.append(resi)
        drug_data = {
            "code": 1,
            "error": "",
            "data": res
        }
        mysql_close(cursor, conn)
        return drug_data
        # # 总共有多少条内容
        # total = len(res)
        # # 用divmod得出总内容条数与每页显示条数的商和余数
        # max_page, a = divmod(total, page)
        # # max_page--总页数，a--剩余条数
        # # print(max_page,a)
        # if a > 0:
        #     max_page = max_page + 1
        # if paper < 1 or paper > max_page:
        #     # print('你输入的页码不存在，请输入1~%s页' % max_page)
        #     drug_data = {
        #         "code": 0,
        #         "error": "暂无数据",
        #         "data": ""
        #     }
        # else:
        #     start = (paper - 1) * page
        #     end = paper * page
        #     drug_data = res[start:end]
        #     drug_data = {
        #         "code": 1,
        #         "error": "",
        #         "data": drug_data
        #     }
        # return jsonify(drug_data)

# 历史搜索
def history(history_data):
    conn = db.connection()
    cursor = conn.cursor()
    openid = history_data['openid']
    delete = history_data['delete']
    if openid:
        if delete == '1':
            try:
                sql = "DELETE FROM history_search where open_id = '%s'" % (openid)
                cursor.execute(sql)
                conn.commit()
            except:
                conn.rollback()
        sql = "select name from history_search where open_id = '%s' ORDER BY update_time desc LIMIT 20" % (openid)
        cursor.execute(sql)
        conn.commit()
        data = cursor.fetchall()
        drug_data = []
        for name in data:
            drug_name = name[0]
            drug_data.append(drug_name)
        drug_data = {
            "code": 1,
            "error": "",
            "data": drug_data
        }
        mysql_close(cursor, conn)
        return drug_data
    else:
        drug_data = {
            "code": 0,
            "error": "openid为空",
            "data": ""
        }
        return drug_data

# 热搜
def hot():
    sql = "select drug_id,drug_name from hot_search order by frequency desc LIMIT 6"
    conn = db.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    data = cursor.fetchall()
    index_dict = get_index_dict(cursor)
    res = []
    # 重新生成字典
    for datai in data:
        resi = {}
        for indexi in index_dict:
            resi[indexi] = datai[index_dict[indexi]]
        res.append(resi)
    drug_data = {
        "code": 1,
        "error": "",
        "data": res
    }
    mysql_close(cursor, conn)
    return drug_data

# 对比
def contrast(datajson):
    conn = db.connection()
    cursor = conn.cursor()
    if datajson['drug_id'] and datajson['contrast_id']:
        sql1 = "select * from drug_instruct where id = '%s' or id = '%s'" %\
               (datajson['drug_id'],datajson['contrast_id'])
        cursor.execute(sql1)
        conn.commit()
        data = cursor.fetchall()
        desc = get_index_dict(cursor)
        res = []
        for datai in data:
            resi = {}
            for indexi in desc:
                resi[indexi] = datai[desc[indexi]]
            res.append(resi)
        drug_data = {
            "code": 1,
            "error": "",
            "data": res
        }
        mysql_close(cursor, conn)
        return drug_data
    else:
        drug_data = {
            "code": 0,
            "error": "最少选择两种药品",
            "data":""
        }
        return drug_data
